﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace DAL.Service.Impl
{
    public class DirectoryListServiceImpl : BaseServiceImpl, IDirectoryListService
    {
        public IList getDirectoryList(string uid)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("SELECT * FROM joy.DirectoryList WHERE dl_uid=@uid");
            query.addParas("@uid", uid);
            return base.getList(query);
        }

        public int delDirectory(string id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("DELETE joy.DirectoryList WHERE dl_id=@id");
            query.addParas("@id", id);
            return base.exeUpdateSql(query);
        }

        public int checkDirectoryName(int uid, string name)
        {
            MyQuery query = new MyQuery();
            query.setQueryString(@"select count(*) as total from joy.DirectoryList  where dl_uid=@id and dl_name=@name");
            query.addParas("@id", uid);
            query.addParas("@name", name);
            IList list = base.getList(query);
            Hashtable tb = (Hashtable)list[0];
            return int.Parse(tb["total"].ToString());
        }
        public int getUserSaveCount(int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select count(*) as total from joy.DirectoryList as a inner join joy.UserSave as b on a.dl_id=b.us_dlid where dl_id=@id");
            query.addParas("@id", id);
            IList list = base.getList(query);
            Hashtable tb = (Hashtable)list[0];
            return int.Parse(tb["total"].ToString());
        }
        //

        public int delUserSave(string ids)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("DELETE joy.UserSave where us_id in (" + ids + ")");
            return base.exeUpdateSql(query);

        }
        //select * from joy.DirectoryList as a inner join joy.UserSave as b on a.dl_id=b.us_dlid inner join joy.LibraryBook as c on b.us_lbid=c.lb_id

        public IList DirectoryList(int uid,int id, int pageno, int pagesize)
        {

            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            string _sql = " where 1 = 1 and dl_uid=@uid and dl_id=@id";
            if (pageno == 1)
            {
                sb.Append("SELECT count(lb_id) as total FROM  joy.DirectoryList as a inner join joy.UserSave as b on a.dl_id=b.us_dlid inner join joy.LibraryBook as c on b.us_lbid=c.lb_id " + _sql + ";");
                sb.Append("select top " + pagesize + " * from  joy.DirectoryList as a inner join joy.UserSave as b on a.dl_id=b.us_dlid inner join joy.LibraryBook as c on b.us_lbid=c.lb_id" + _sql + "  order by us_id desc;");
            }
            else
            {
                sb.Append("select * from(");
                sb.Append("select *,ROW_NUMBER() OVER (order by us_id desc) as rno FROM joy.DirectoryList as a inner join joy.UserSave as b on a.dl_id=b.us_dlid inner join joy.LibraryBook as c on b.us_lbid=c.lb_id " + _sql);
                sb.Append(") as d where d.rno>@start and d.rno<@end  order by us_id desc;");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.addParas("@id", id);
            query.addParas("@uid", uid);
            query.setQueryString(sb.ToString());
            return base.getList(query);
        }
    }
}
